﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
///db_teacher 的摘要说明
/// </summary>
public class db_teacher
{
    SqlDBClass db;
	public db_teacher()
	{
        db = new SqlDBClass();
	}

    /// <summary>
    /// 查询所有 教师
    /// </summary>
    /// <returns></returns>
    public DataSet selectTeacherALL(string status) {
        //teacher
        string strWhere="";
        if (!status.Equals(""))
        {
            strWhere = " and status ='" + status + "'";
        }
        string strsql = "select teacherId, teacherName ,about,picUrl, statusCode,statusName from teacher,status where teacher.status=status.statuscode"+strWhere;
        return db.execSql(strsql);
    }

    /// <summary> /// 查询单个 教师 /// </summary> /// <param name="teacherId"></param>
     /// <returns></returns> 
    public DataSet selectTeacherONE(string teacherId) { //teacher 
        string strsql = "select teacherId, teacherName ,about,picUrl,statusCode,statusName from teacher,status where teacher.status=status.statuscode and teacherId=" + teacherId; ; 
    return db.execSql(strsql); }


		//搜索教师
    public DataSet selectTeacher_search(string teacherId,string teacherName,string about,string status){
        string strsql = "select teacherId,teacherName,about,picUrl,statusCode,statusName from teacher,status where  teacher.status=status.statuscode ";
        string strWhere = "and teacherId like '%" + teacherId + "%'";
        strWhere += " and teacherName like '%" + teacherName + "%'";
        strWhere += " and about like '%" + about + "%'";
        if (!status.Equals(""))
        {
            strWhere += " and status ='" + status + "'";
        }
        strsql += strWhere;
        return db.execSql(strsql);
    }

    /// <summary>
    /// 删除教师
    /// </summary>
    /// <param name="teacherId"></param>
    /// <returns></returns>
    public Boolean delTeacher(string teacherId)
    {  
        string strsql = "delete from teacher where teacherId=" + teacherId;
        return db.execSqlNoQuery(strsql);
    }

    /// <summary>
    /// 增加教师
    /// </summary>
    /// <param name="teacherId"></param>
    /// <param name="teacherName"></param>
    /// <param name="about"></param>
    /// <param name="picUrl"></param>
    /// <param name="pwd"></param>
    /// <returns></returns>
    public Boolean addTeacher(string teacherId,string teacherName,string about,string picUrl,string pwd) {
        string strsql = "Insert Into teacher(teacherId,teacherName,about,picUrl,pwd,status) Values('" + teacherId + "','" + teacherName + "','" + about + "','" + picUrl + "','" + Tools.mymd5(pwd) + "','0')";
        return db.execSqlNoQuery(strsql);
    }



    /// <summary>
    /// 更新教师
    /// </summary>
    /// <param name="teacherId"></param>
    /// <param name="teacherName"></param>
    /// <param name="about"></param>
    /// <param name="picUrl"></param>
    /// <returns></returns>
    public Boolean updateTeacher(string teacherId,string teacherName,string about,string picUrl ,string status)
    {
        string strsql = "update teacher set teacherName='" + teacherName + "',about='" + about + "',picUrl='" + picUrl + "',status='" + status +"' where teacherId=" + teacherId;
        return db.execSqlNoQuery(strsql);
    }
    
    public string[] isLogin(string username, string pwd)
    {
        string[] info = new string[4];
        DataSet ds = checkPwdDS(username, pwd);
        if (ds != null)
        {
            info[0] = ds.Tables[0].Rows[0]["teacherName"].ToString();
            info[1] = ds.Tables[0].Rows[0]["lastTime"].ToString();
            info[2] = ds.Tables[0].Rows[0]["lastIp"].ToString();
            //info[3] = ds.Tables[0].Rows[0][3].ToString();
            // info[4] = ds.Tables[0].Rows[0][4].ToString();
            if (updateTimeIP(username))
                return info;
            else
                return null;
        }
        else
        {
            return null;
        }
    }
    
    /// <summary>
    /// 检查密码是否正确
    /// </summary>
    /// <param name="username"></param>
    /// <param name="pwd"></param>
    /// <returns></returns>
    public Boolean checkPwd(string username, string pwd) {
        string sql = "select * from teacher where teacherId='" + username + "' and pwd='" + pwd + "'";
        DataSet ds = new DataSet();
        ds = db.execSql(sql);
        if ((ds.Tables.Count != 0) && (ds.Tables[0].Rows.Count == 1))
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
    public DataSet checkPwdDS(string username, string pwd)
    {
        string sql = "select * from teacher where status = '0' and teacherId='" + username + "' and pwd='" + pwd + "'";
        DataSet ds = new DataSet();
        ds = db.execSql(sql);
        if ((ds.Tables.Count != 0) && (ds.Tables[0].Rows.Count == 1))
        {
            return ds;
        }
        else
        {
            return null;
        }
    }

    

    public Boolean updateTimeIP(string name)
    {
        string strsql = "update teacher set lastIP='" + HttpContext.Current.Request.UserHostAddress + "',lastTime='" + DateTime.Now + "'where  teacherId='" + name + "'";
        return db.execSqlNoQuery(strsql);
    }
    
     /// <summary>
    /// 重设密码
    /// </summary>
    /// <param name="teacherId"></param>
    /// <param name="pwd"></param>
    /// <returns></returns>
    public Boolean setPwd(string teacherId, string pwd)
    {
        string strsql = "update teacher set pwd='" + pwd + "'where  teacherId='" + teacherId + "'";
        return db.execSqlNoQuery(strsql);
    }

    public string getNewTeacherId()
    {
        string strsql = "select  coalesce(nullif(max(convert(int,TeacherId)),''),'0')+1 as teacherId  from teacher";
    	DataSet ds = new DataSet();
			string teacherId = "";
			ds = db.execSql(strsql);
				
			if (ds.Tables.Count != 0)
			{
                teacherId = ds.Tables[0].Rows[0]["teacherId"].ToString();
			}
				
			return teacherId;
    }

    /// <summary>
    /// 获取热门老师
    /// </summary>
    /// <param name="top"></param>
    /// <returns></returns>
    public DataSet selectTeacherHit_Top(string top)
    {
        string strsql = "select top " + top + " count(teacherName) as num,t.teacherid,t.teacherName from course c,coursesell cs,teacher t where c.courseid=cs.courseid and c.teacherid=t.teacherid group by t.teacherName,t.teacherid,t.teacherName order by t.teacherid";
        return db.execSql(strsql);
    }
}